﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using UniMana.Utils.Controls;
using UniMana.Utils.Configs;
using System.Data.Linq;
using UniMana.Data;

namespace UniMana.Data
{
    public static class Training_SubjectOpenRequest_Teacher_StudentData
    {
        public static IQueryable GetListByRef(
            this Table<Training_SubjectOpenRequest_Teacher_Student> 
                    nTraining_SubjectOpenRequest_Teacher_Student, 
            Table<Training_Student> nTraining_Student,
            Table<Training_Student_Class> nTraining_Student_Class, 
            int _iTraining_SubjectOpenRequestKey, 
            int _iTraining_ClassKey)
        {
            var iListStudent = 
                nTraining_SubjectOpenRequest_Teacher_Student.Where(
                p => p.Training_SubjectOpenRequestKey == _iTraining_SubjectOpenRequestKey && 
                    p.Training_ClassKey == _iTraining_ClassKey);
            var iListStudentDetail = from student in nTraining_Student
                                     from studentClass in nTraining_Student_Class
                                     where student.Training_StudentKey == studentClass.Training_StudentKey
                                     where studentClass.Training_ClassKey == _iTraining_ClassKey
                                     select new
                                     {
                                         studentClass.Training_StudentKey,
                                         studentClass.ID,
                                         FullName =
                                            student.LastName.TrimEnd() + " " + student.SureName.TrimStart(),
                                         Gender = student.Gender ==
                                            UniMana.Utils.Configs.DefaultConfig.FEMALE ? "Nữ" : "Nam",
                                         Birthday = student.Birthday == null ?
                                            student.ExceptionBirthday :
                                            student.Birthday.ToString()
                                     };
            var iLastQuery = from listStudent in iListStudent
                             from listStudentDetail in iListStudentDetail
                             where listStudent.Training_StudentKey == 
                                listStudentDetail.Training_StudentKey
                             select new
                             {
                                 listStudentDetail.Training_StudentKey,
                                 listStudentDetail.ID,
                                 listStudentDetail.FullName,
                                 listStudentDetail.Gender,
                                 listStudentDetail.Birthday
                             };
            return iLastQuery;
        }

        public static IQueryable GetByReuqestOpenKey(
            this Table<Training_SubjectOpenRequest_Teacher_Student> 
                nTraining_SubjectOpenRequest_Teacher_Student, 
            Table<Training_Class> nTraining_Classes,
            int _iTraining_SubjectOpenRequestKey)
        {
            var iQTeacherStudent = from teacherStudent in nTraining_SubjectOpenRequest_Teacher_Student
                                   where teacherStudent.Training_SubjectOpenRequestKey == 
                                    _iTraining_SubjectOpenRequestKey
                                   group teacherStudent by new
                                   {
                                       teacherStudent.Training_SubjectOpenRequestKey,
                                       teacherStudent.Training_ClassKey,
                                       teacherStudent.Training_TrainingProgramKey
                                       //teacherStudent.Training_StudentKey
                                   } into g
                                   select new
                                   {
                                       g.Key.Training_SubjectOpenRequestKey,
                                       g.Key.Training_ClassKey,
                                       g.Key.Training_TrainingProgramKey,
                                       Totals = g.Count()
                                   };
            var iQLastQuery = from teacherStudent in iQTeacherStudent
                              from tClass in nTraining_Classes
                              where tClass.Training_ClassKey == 
                                teacherStudent.Training_ClassKey
                              select new
                              {
                                  teacherStudent.Training_ClassKey,
                                  tClass.ID,
                                  tClass.Name,
                                  teacherStudent.Totals
                              };
            return iQLastQuery;
        }
        public static void DeleteBySubjectOpenRequestKey(
            this Table<Training_SubjectOpenRequest_Teacher_Student> 
                nTraining_SubjectOpenRequest_Teacher_Student, 
            int _iTraining_SubjectOpenRequestKey)
        {
            DBMain dbTraining = new DBMain();
            var iQuery = 
                dbTraining.m_DCSave.Training_SubjectOpenRequest_Teacher_Students.Where(
                p => p.Training_SubjectOpenRequestKey == _iTraining_SubjectOpenRequestKey);
            dbTraining.m_DCSave.Training_SubjectOpenRequest_Teacher_Students.DeleteAllOnSubmit(iQuery);
            dbTraining.m_DCSave.SubmitChanges();
        }
    }
}
